/**
   Copyright 2013 Фроловский А. В.

   Licensed under the Apache License, Version 2.0 (the "License");
   you may not use this file except in compliance with the License.
   You may obtain a copy of the License at

       http://www.apache.org/licenses/LICENSE-2.0

   Unless required by applicable law or agreed to in writing, software
   distributed under the License is distributed on an "AS IS" BASIS,
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
   See the License for the specific language governing permissions and
   limitations under the License.
   
   This file is a part of MyStats project
 */

package foo.mystats.db;

import android.database.sqlite.SQLiteDatabase;

public class RowTable {
  public static final String TABLE_ROW = "row";
  public static final String COLUMN_ID = "id";
  public static final String COLUMN_NAME = "name";
  public static final String COLUMN_DESCRIPTION = "description";
  public static final String COLUMN_IS_TIME_NEEDED = "is_time_needed";
  public static final String COLUMN_DATA_TYPE = "data_type";

  private static final String SQL_CREATE = "CREATE TABLE " + TABLE_ROW + " (\r\n" +
      COLUMN_ID + " INTEGER PRIMARY KEY,\r\n" +
      COLUMN_NAME + " TEXT UNIQUE NOT NULL,\r\n" +
      COLUMN_DESCRIPTION + " TEXT,\r\n" +
      COLUMN_IS_TIME_NEEDED + " INTEGER NOT NULL,\r\n" +
      COLUMN_DATA_TYPE + " INTEGER NOT NULL\r\n" +
      ");";

  private static final String SQL_CREATE_DELETE_TRIGGER = "CREATE TRIGGER tr_" + TABLE_ROW + "_bd\r\n" +
      "  BEFORE DELETE ON " + TABLE_ROW + "\r\n" +
      "  FOR EACH ROW BEGIN\r\n" +
      "      DELETE FROM " + DataTable.TABLE_DATA + " WHERE " + DataTable.COLUMN_ROW_ID + " = OLD.id;\r\n" +
      "  END;";

  private static final String SQL_DELETE_ORPHAN_DATA = "DELETE FROM " + DataTable.TABLE_DATA + "\r\n" +
      "  WHERE NOT EXISTS (SELECT NULL FROM " + RowTable.TABLE_ROW + " r WHERE r." + RowTable.COLUMN_ID +
      " = " + DataTable.COLUMN_ROW_ID + ")";

  public static void onCreate(SQLiteDatabase db) {
    db.execSQL(SQL_CREATE);
    db.execSQL(SQL_CREATE_DELETE_TRIGGER);
  }

  public static void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    if (oldVersion == 1 && newVersion == 2) {
      db.execSQL(SQL_DELETE_ORPHAN_DATA);
      db.execSQL(SQL_CREATE_DELETE_TRIGGER);
    }
  }
}
